﻿Imports System.Data.SqlClient
Imports System.Data
Public Class DataProvider

    ' Shared variables
    Dim _strConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\CSDL\QuanLyNhaHang.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
    Public con As New SqlConnection(_strConnectionString)
    Dim myDA As SqlDataAdapter
    Dim cmd As SqlCommand
    Dim _error As String = ""
    Property Loi As String
        Get
            Return _error
        End Get
        Set(ByVal value As String)
            _error = value
        End Set
    End Property
    
    'Binding database table to DataGridView
    Public Function GetData(ByVal sql As String) As Object
        OpenConnect()
        cmd = New SqlCommand(sql, con)
        Dim str As String = ""
        If OpenConnect() = True Then
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                str = reader.Item(0)
                str = str.Trim(" ")
                Return str
            End While
        End If
        Return str

    End Function
    Public Function ShowData(ByVal sql As String) As DataTable
        OpenConnect()
        cmd = New SqlCommand(sql, con)
        Dim dt As DataTable = Nothing
        If OpenConnect() = True Then
            myDA = New SqlDataAdapter(cmd)
            dt = New DataTable()
            myDA.Fill(dt)
            Return dt
        End If
        Return dt
    End Function
    Public Function OpenConnect() As Boolean
        Try
            If con Is Nothing Then
                con = New SqlConnection(_strConnectionString)
            End If
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
            con.Open()
            Return True
        Catch ex As Exception
            Loi = ex.Message
        End Try
        Return False
    End Function
    Public Function CloseConnect() As Boolean
        Try
            If con IsNot Nothing Then
                If con.State = ConnectionState.Open Then
                    con.Close()

                End If

            End If
            Return True
        Catch ex As Exception
            Loi = ex.Message
            Return False
        End Try

    End Function

    Public Function SqlExcuteNonQuery(ByVal sql As String) As Boolean
        Try
            OpenConnect()
            cmd = New SqlCommand(sql, con)
            cmd.ExecuteNonQuery()
            Loi = ""
            Return True
        Catch ex As Exception
            Loi = ex.Message
        Finally
            CloseConnect()
        End Try
        Return False
    End Function

End Class
